﻿Namespace Functions
    Public Class MetisTableFunction
        Inherits MetisResultFunction
        Implements System.ComponentModel.INotifyPropertyChanged, Flows.IMetisFlowNode, IMetisExecutable


        Public Shadows Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged

        Sub New(ByVal MetisCore As MetisCore, Optional ByVal [Namespace] As MetisNamespace = Nothing)
            _MetisCore = MetisCore
            _Namespace = [Namespace]
            _ID = _MetisCore.GetTemporaryID
            _Name = String.Format(My.Resources.Metis_Dim_NewName.ToString, _ID * -1)
            _Table = New DB.DBTable(_MetisCore, "", DB.DBTableType.Table)
            _IsModified = True
            _LoadState = MetisLoadState.NewDefinition

            _UsedByFunctions = New MetisFunctionList(_MetisCore, Me)
            _UsesFunctions = New MetisFunctionList(_MetisCore, Me)

            _Joins = New MetisJoinList()
            _FunctionReferences = New MetisRefFunctionList(_MetisCore, Me)
            _OpSets = New MetisSetOpList(_MetisCore, Me)

            _BackupJoins = New MetisJoinList()
            _BackupFunctionReferences = New MetisRefFunctionList(_MetisCore, Me)
            _BackupOpSets = New MetisSetOpList(_MetisCore, Me)

            
        End Sub


        Sub New(ByVal MetisCore As MetisCore, ByVal [Namespace] As MetisNamespace, ByVal ID As Integer, ByVal Name As String, ByVal Annotation As String, _
                ByVal Table As DB.DBTable, ByVal ModifiedOn As Date, ByVal CreatedOn As Date, ByVal Level As Integer, ByVal Action As MetisFunctionAction)

            _MetisCore = MetisCore
            _Namespace = [Namespace]
            _UsedByFunctions = New MetisFunctionList(_MetisCore, Me)
            _UsesFunctions = New MetisFunctionList(_MetisCore, Me)
            _ID = ID
            _Name = Name
            _BackupName = Name
            _Annotation = Annotation
            _BackupAnnotation = Annotation
            _Table = Table
            _ModifiedOn = ModifiedOn
            _CreatedOn = CreatedOn
            _Level = Level
            _LoadState = MetisLoadState.NotLoaded
            _Action = Action

            _Joins = New MetisJoinList()
            _FunctionReferences = New MetisRefFunctionList(_MetisCore, Me)
            _OpSets = New MetisSetOpList(_MetisCore, Me)

            _BackupJoins = New MetisJoinList()
            _BackupFunctionReferences = New MetisRefFunctionList(_MetisCore, Me)
            _BackupOpSets = New MetisSetOpList(_MetisCore, Me)
        End Sub


        Public Overrides Sub Save()
            Dim OraCmd As OracleCommand

            'remove all unused table fields
            If _Table.FieldList.Count > 0 Then
                For i As Integer = _Table.FieldList.Count - 1 To 0
                    If Not Me._Fields.ContainsDBField(_Table.FieldList(i).DBfieldName) Then
                        _Table.FieldList.RemoveAt(i)
                    End If
                Next
            End If

            'add new and update existing fields
            For Each item As MetisFunctionField In _Fields
                If _Table.FieldList.ContainsFieldName(item.DBFieldName) Then
                    _Table.FieldList(item.DBFieldName) = item.DBField
                Else
                    _Table.FieldList.Add(item.DBField)
                End If
            Next


            Dim SQLscript As String = ""
            Dim sSetString As String = ""
            Dim tmpFieldID As Integer


            If _LoadState = MetisLoadState.NewDefinition Then
                _ID = _MetisCore.GetNextID
                _Table.TableName = "FUNC_" & _ID

                SQLscript &= String.Format("INSERT INTO dam_functions (function_id, name, table_name, func_type) VALUES ({0}, '{1}', '{2}', 2);", _ID, _Name, _Table.TableName) & vbCrLf
                SQLscript &= "COMMIT;" & vbCrLf

                sSetString = ""
                For Each item As MetisFunctionField In _Fields
                    If item.PK Then sSetString &= "," & item.DBFieldName
                Next
                If sSetString <> "" Then
                    SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {1} ADD CONSTRAINT FUNC_{0}_PK PRIMARY KEY ({2})';", _ID, _Table.TableName, sSetString.Substring(1)) & vbCrLf
                End If


            ElseIf _Name <> _BackupName Or _Annotation <> _BackupAnnotation Or _Action <> _BackupAction Or _
               _Having <> _BackupHaving Or _Where <> _BackupWhere Or _
               _UseDistinct <> _BackupUseDistinct Or _UseGroupBy <> _BackupUseGroupBy Then

                sSetString = ""
                If _Name <> _BackupName Then sSetString &= ",name='" & _Name & "'"
                If _Annotation <> _BackupAnnotation Then sSetString &= ",description='" & _Annotation.Replace("'", "''") & "'"
                If _Action <> _BackupAction Then sSetString &= ",action=" & _Action
                If _Having <> _BackupHaving Then sSetString &= ",Having_Condition='" & _Having.Replace("'", "''") & "'"
                If _Where <> _BackupWhere Then sSetString &= ",Where_Condition='" & _Where.Replace("'", "''") & "'"
                If _UseDistinct <> _BackupUseDistinct Then sSetString &= ",Use_Distinct=" & _UseDistinct
                If _UseGroupBy <> _BackupUseGroupBy Then sSetString &= ",Use_Group_By=" & _UseGroupBy
                SQLscript &= String.Format("UPDATE dam_functions SET {1} " & "WHERE function_id={0};", _ID, sSetString.Substring(1)) & vbCrLf
            End If

            'Remove Fields that are requested to be removed
            For Each item As MetisFunctionField In _BackupFields
                If Not _Fields.Contains(item.ID) Then
                    SQLscript &= String.Format("DELETE FROM dam_function_fields WHERE function_field_id={0};", item.ID) & vbCrLf
                End If
            Next

            'Add new Fields and update existing fields
            SQLscript &= "DAM_DISABLE_CONSTRAINTS('dam_function_fields');" & vbCrLf
            For Each item As MetisFunctionField In _Fields
                If _BackupFields.Contains(item.ID) Then
                    sSetString = ""
                    Dim OldField As MetisFunctionField = _BackupFields.FieldByID(item.ID)
                    If item.Name <> OldField.Name Then sSetString &= ",field_name='" & item.Name & "'"
                    If item.DBFieldName <> OldField.DBFieldName Then sSetString &= ",db_field_name='" & item.DBFieldName.ToUpper & "'"
                    If item.Annotation <> OldField.Annotation Then sSetString &= ",description='" & item.Annotation.Replace("'", "''") & "'"
                    If _Fields.IndexOf(item) <> _BackupFields.IndexOf(OldField) Then sSetString &= ",seq=" & _Fields.IndexOf(item)
                    If item.Expression <> OldField.Expression Then sSetString &= ",expression='" & item.Annotation.Replace("'", "''") & "'"
                    If item.FieldType <> OldField.FieldType Then sSetString &= ",field_type=" & If(item.FieldType = MetisFunctionFieldType.KeyField, 1, 0)
                    If item.DimensionID <> OldField.DimensionID Then sSetString &= ",dimension_fk=" & If(item.DimensionID < 0, "NULL", item.DimensionID)
                    If item.ParentFieldID <> OldField.ParentFieldID Then sSetString &= ",parent_field=" & If(item.ParentFieldID < 0, "NULL", item.ParentFieldID)


                    If sSetString <> "" Then SQLscript &= String.Format("UPDATE dam_function_fields SET {1} " & _
                                                                        "WHERE function_field_id={0};", item.ID, sSetString.Substring(1)) & vbCrLf
                Else
                    tmpFieldID = _MetisCore.GetNextFieldID
                    SQLscript &= String.Format("INSERT INTO dam_function_fields (function_field_id, field_name, db_field_name, function_fk, seq, field_type, expression, dimension_fk, parent_field) " & _
                                               "VALUES ({0}, '{1}', '{2}', {3}, {4}, {5}, '{6}', {7}, {8});", _
                                               tmpFieldID, item.Name, item.DBFieldName, _ID, _Fields.IndexOf(item), _
                                               If(item.FieldType = MetisFunctionFieldType.KeyField, 1, 0), _
                                               item.Expression.Replace("'", "''"), _
                                               If(item.DimensionID < 0, "NULL", item.DimensionID), _
                                               If(item.ParentFieldID < 0, "NULL", item.ParentFieldID)) & vbCrLf
                End If
            Next
            SQLscript &= "DAM_ENABLE_CONSTRAINTS('dam_function_fields');" & vbCrLf

            'Add/Update/Remove Function-References
            For Each item As MetisRefFunction In _BackupFunctionReferences
                If Not _FunctionReferences.Contains(item) Then
                    SQLscript &= String.Format("DELETE FROM dam_function_ref WHERE func_ref_id={0};", item.ID) & vbCrLf
                End If
            Next
            For Each item As MetisRefFunction In _FunctionReferences
                If _BackupFunctionReferences.Contains(item) Then
                    SQLscript &= String.Format("UPDATE dam_function_ref SET alias='{1}', pos_x={2}, pos_y={3} " & _
                                               "WHERE func_ref_id={0};", item.ID, item.Alias, item.Position.X, item.Position.Y) & vbCrLf
                Else
                    SQLscript &= String.Format("INSERT INTO dam_function_ref (func_ref_id, function_fk, uses_function_fk, alias, pos_x, pos_y) " & _
                                               "VALUES ({0},{1},{2},'{3}',{4},{5});", _
                                               item.GetNextID, _ID, item.ReferencedFunction.ID, _
                                               item.Alias, item.Position.X, item.Position.Y) & vbCrLf
                End If
            Next

            'Save Joins
            SQLscript &= String.Format("DELETE FROM dam_function_joins WHERE function_fk={0};", _ID) & vbCrLf
            For Each item As MetisJoin In _Joins
                SQLscript &= String.Format("INSERT INTO dam_function_joins " & _
                                           "(function_fk, left_func_ref_fk, left_field_fk, right_func_ref_fk, right_field_fk, join_type) " & _
                                           "VALUES ({0},{1},{2},{3},{4},{5});", _ID, item.LeftFuncRef.ID, item.LeftField.ID, _
                                            item.RightFuncRef.ID, item.RightField.ID, _
                                            If(item.JoinType = MetisJoinType.InnerJoin, 0, If(item.JoinType = MetisJoinType.LeftJoin, -1, 1))) & vbCrLf
            Next
            'TODO Save OpSet

            _Table.Save()

            If SQLscript <> "" Then
                SQLscript = String.Format("--- Save {1} ---{2}BEGIN{2}{0}COMMIT;{2}END;{2}", SQLscript, _Name, vbCrLf)
                OraCmd = New OracleCommand(SQLscript, _MetisCore.Connection)
                Debug.Print(SQLscript)
                OraCmd.ExecuteNonQuery()
            End If

            _LoadState = MetisLoadState.NotLoaded
            Reload()
            If _MetisCore.Functions.Find(_ID) Is Nothing Then
                _MetisCore.Functions.Add(Me)
            End If
            RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Name"))

        End Sub

        Public Sub Execute() Implements IMetisExecutable.Execute
            'TODO Execute of TableFunc (generate SQL, create Temptable, merge into table)
        End Sub

        Public Overrides ReadOnly Property FunctionType() As MetisFunctionType
            Get
                Return MetisFunctionType.Table
            End Get
        End Property

        Public Property Action() As MetisFunctionAction
            Get
                Return _Action
            End Get
            Set(ByVal value As MetisFunctionAction)
                _Action = value
                RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Action"))
            End Set
        End Property

        Public ReadOnly Property HasChildren As Boolean Implements Flows.IMetisFlowNode.HasChildren
            Get
                Return False
            End Get
        End Property
    End Class
End Namespace